<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.ArrayList" %>
<%@ page import="java.lang.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.W3.org/TR/thm14/loose.dtd">
<html>
<head>
<title>data</title>
<style>
	table{
		width:100%;
		border-collapse:collapse;		
	}
	table caption{
		font-size:2em;
		font-weight:bold;
		margin:lem 0;
	}
	th,td{
		border: 1px solid #999;
		text-align:center;
		padding:20px 0;
	}
	table thead tr{
		background-color:#008c8c;
		color:#fff;
	}
	table tbody tr:nth-child(odd){
		background-color:#eee;
	}
	table tbody tr:hover{
		background-color:#ccc;	
	}
	table tbody tr td:first-child{
		color:#f40;
	}
	table tfoot tr td{
		text-align:center;
		padding-rignt:20px;	
	}
</style>
</head>
<body>
	<center>
		<%!ArrayList<String> list1 = new ArrayList<String>(); %>
		<%!ArrayList<Integer> list2 = new ArrayList<Integer>(); %>
		<%!ArrayList<Double> list3 = new ArrayList<Double>(); %>
		<%!ArrayList<Integer> list4 = new ArrayList<Integer>(); %>
		<%!ArrayList<Double> list5 = new ArrayList<Double>(); %>
		<%!ArrayList<Integer> list6 = new ArrayList<Integer>(); %>
		<%!ArrayList<Double> list7 = new ArrayList<Double>(); %>
		<%!ArrayList<Integer> list8 = new ArrayList<Integer>(); %>
		<%!ArrayList<Double> list9 = new ArrayList<Double>(); %>
		<%!ArrayList<Integer> list10 = new ArrayList<Integer>(); %>
		<%!ArrayList<Double> list11 = new ArrayList<Double>(); %>
		<%
			try{
				Class.forName("dm.jdbc.driver.DmDriver");
				String url = "jdbc:dm://127.0.0.1:5236";
				String username = "SYSDBA";
				String password = "SYSDBA";
				Connection conn = DriverManager.getConnection(url,username,password);
				System.out.println("连接成功!");
		%>
		<table border="0" cellspacing="0" cellpadding="0">
			<caption>2015~2020总数据预览</caption>
			<thead>
			<tr>
				<th>年月</th>
				<th>总人数</th>
				<th>同比增长</th>
				<th>香港入境人数</th>
				<th>同比增长</th>
				<th>台湾入境人数</th>
				<th>同比增长</th>
				<th>澳门入境人数</th>
				<th>同比增长</th>
				<th>外国入境人数</th>
				<th>同比增长</th>
			</tr>
			</thead>
			<%
				Statement stmt = null;
				ResultSet rs1 = null;
				ResultSet rs2 = null;
				ResultSet rs3 = null;
				ResultSet rs4 = null;
				ResultSet rs5 = null;
				String sql1 = "select * from total;";
				String sql2 = "select * from xianggang;";
				String sql3 = "select * from taiwan;";
				String sql4 = "select * from aomen;";
				String sql5 = "select * from foreigners;";
				stmt = conn.createStatement();
				rs1 = stmt.executeQuery(sql1);
				while(rs1.next()){
					String a;
					double c;
					int b;
					a = rs1.getString("年月");
					b = rs1.getInt("人数");
					c = rs1.getDouble("同比增长");
					list1.add(a);
					list2.add(b);
					list3.add(c);
				}
				rs2 = stmt.executeQuery(sql2);
				while(rs2.next()){
					double c;
					int b;
					b = rs2.getInt("人数");
					c = rs2.getDouble("同比增长");
					list4.add(b);
					list5.add(c);
				}
				rs3 = stmt.executeQuery(sql3);
				while(rs3.next()){
					double c;
					int b;
					b = rs3.getInt("人数");
					c = rs3.getDouble("同比增长");
					list6.add(b);
					list7.add(c);
				}
				rs4 = stmt.executeQuery(sql4);
				while(rs4.next()){
					double c;
					int b;
					b = rs4.getInt("人数");
					c = rs4.getDouble("同比增长");
					list8.add(b);
					list9.add(c);
				}
				rs5 = stmt.executeQuery(sql5);
				while(rs5.next()){
					double c;
					int b;
					b = rs5.getInt("人数");
					c = rs5.getDouble("同比增长");
					list10.add(b);
					list11.add(c);
				}
			}catch(Exception e){
				e.printStackTrace();
				System.out.println("数据库连接失败!");
			}
			for(int i = 6; i <= 12; i ++ ){
				int len = list2.size();
				double rote_huanbi = (list2.get(len - 1) - list2.get(len - 2)) * 1.0 / list2.get(len - 2); 
				int sum = (int)((list2.get(len - 1) * (rote_huanbi + 1) + (list3.get(len - 12) + 1) * list2.get(len - 12) * 0.01) / 2);
				if(sum < 0)	sum *= -1;
				if(i == 6){
					list1.add("2020.06");
				}else if(i == 7){
					list1.add("2020.07");
				}else if(i == 8){
					list1.add("2020.08");
				}else if(i == 9){
					list1.add("2020.09");
				}else if(i == 10){
					list1.add("2020.10");
				}else if(i == 11){
					list1.add("2020.11");
				}else if(i == 12){
					list1.add("2020.12");
				}
				list2.add(sum);
				double rote_tongbi = (sum - list2.get(len - 12)) * 1.0 / list2.get(len - 12) * 100;
				list3.add(rote_tongbi);
			}
			for(int i = 6; i <= 12; i ++ ){
				int len = list4.size();
				double rote_huanbi = (list4.get(len - 1) - list4.get(len - 2)) * 1.0 / list4.get(len - 2); 
				int sum = (int)((list4.get(len - 1) * (rote_huanbi + 1) + (list5.get(len - 12) + 1) * list4.get(len - 12) * 0.01) / 2);
				if(sum < 0)	sum *= -1;
				list4.add(sum);
				double rote_tongbi = (sum - list4.get(len - 12)) * 1.0 / list4.get(len - 12) * 100;
				list5.add(rote_tongbi);
			}
			for(int i = 6; i <= 12; i ++ ){
				int len = list6.size();
				double rote_huanbi = (list6.get(len - 1) - list6.get(len - 2)) * 1.0 / list6.get(len - 2); 
				int sum = (int)((list6.get(len - 1) * (rote_huanbi + 1) + (list7.get(len - 12) + 1) * list6.get(len - 12) * 0.01) / 2);
				if(sum < 0)	sum *= -1;
				list6.add(sum);
				double rote_tongbi = (sum - list6.get(len - 12)) * 1.0 / list6.get(len - 12) * 100;
				list7.add(rote_tongbi);
			}
			for(int i = 6; i <= 12; i ++ ){
				int len = list8.size();
				double rote_huanbi = (list8.get(len - 1) - list8.get(len - 2)) * 1.0 / list8.get(len - 2); 
				int sum = (int)((list8.get(len - 1) * (rote_huanbi + 1) + (list9.get(len - 12) + 1) * list8.get(len - 12) * 0.01) / 2);
				if(sum < 0)	sum *= -1;
				list8.add(sum);
				double rote_tongbi = (sum - list8.get(len - 12)) * 1.0 / list8.get(len - 12) * 100;
				list9.add(rote_tongbi);
			}
			for(int i = 6; i <= 12; i ++ ){
				int len = list10.size();
				double rote_huanbi = (list10.get(len - 1) - list10.get(len - 2)) * 1.0 / list10.get(len - 2); 
				int sum = (int)((list10.get(len - 1) * (rote_huanbi + 1) + (list11.get(len - 12) + 1) * list10.get(len - 12) * 0.01) / 2);
				if(sum < 0)	sum *= -1;
				list10.add(sum);
				double rote_tongbi = (sum - list10.get(len - 12)) * 1.0 / list10.get(len - 12) * 100;
				list11.add(rote_tongbi);
			}
			%>
			<tbody>
			<% for(int i = 0; i < list1.size(); i ++ ){ %>
			<tr>
				<td><% out.print(list1.get(i)); %></td>
				<td><% out.print(list2.get(i)); %></td>
				<td><% out.print(String.format("%.2f",list3.get(i))); %></td>
				<td><% out.print(list4.get(i)); %></td>
				<td><% out.print(String.format("%.2f",list5.get(i))); %></td>
				<td><% out.print(list6.get(i)); %></td>
				<td><% out.print(String.format("%.2f",list7.get(i))); %></td>
				<td><% out.print(list8.get(i)); %></td>
				<td><% out.print(String.format("%.2f",list9.get(i))); %></td>
				<td><% out.print(list10.get(i)); %></td>
				<td><% out.print(String.format("%.2f",list11.get(i))); %></td>				
			</tr>
			<% } %>
			</tbody>
			<tfoot>
				<tr>
					<td colspan="11">数据来源：上海公共数据开放平台</td>
				</tr>
			</tfoot>
		</table>
	</center>
</body>
</html>